using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// Summary description for BillBll
/// </summary>
public class BillBll
{
    DBAccess db = new DBAccess();

    public BillBll()
    {

    }
        
    public DataTable getAllBill()
    {
        return db.executeSelect("GetAllBill");
    }

    public DataTable getBillIsPaid()
    {
        return db.executeSelect("GetBillIsPaid");
    }

    public DataTable getBillIsNotPaid()
    {
        return db.executeSelect("GetBillNotPaid");
    }

    public Bill getBillById(int BillId)
    {
        Bill Bill = new Bill();
        DataTable dt = new DataTable();
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@BillId", SqlDbType.Int);
        para[0].Value = BillId;
        dt = db.executeSelect("GetBillById", para);
        Bill.BillId = int.Parse(dt.Rows[0][0].ToString());
        Bill.PolicyId = int.Parse(dt.Rows[0][1].ToString());
        Bill.Cost = float.Parse(dt.Rows[0][2].ToString());
        Bill.Note = dt.Rows[0][3].ToString();
        Bill.DateOfCreate = DateTime.Parse(dt.Rows[0][4].ToString());
        Bill.IsPaid = bool.Parse(dt.Rows[0][5].ToString());
        return Bill;
    }

    public Bill getBillByPolicyId(int PolicyId)
    {
        Bill Bill = new Bill();
        DataTable dt = new DataTable();
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@PolicyId", SqlDbType.Int);
        para[0].Value = PolicyId;
        dt = db.executeSelect("GetBillByPolicyId", para);
        Bill.BillId = int.Parse(dt.Rows[0][0].ToString());
        Bill.PolicyId = int.Parse(dt.Rows[0][1].ToString());
        Bill.Cost = float.Parse(dt.Rows[0][2].ToString());
        Bill.Note = dt.Rows[0][3].ToString();
        Bill.DateOfCreate = DateTime.Parse(dt.Rows[0][4].ToString());
        Bill.IsPaid = bool.Parse(dt.Rows[0][5].ToString());
        return Bill;
    }

    public bool insertBill(Bill Bill)
    {
        bool result;
        SqlParameter[] para = new SqlParameter[5];
        para[0] = new SqlParameter("@PolicyId", SqlDbType.Int);
        para[0].Value = Bill.PolicyId;
        para[1] = new SqlParameter("@Cost", SqlDbType.Float);
        para[1].Value = Bill.Cost;
        para[2] = new SqlParameter("@Note", SqlDbType.NVarChar);
        para[2].Value = Bill.Note;
        para[3] = new SqlParameter("@DateOfCreate", SqlDbType.DateTime);
        para[3].Value = Bill.DateOfCreate;        
        para[4] = new SqlParameter("@IsPaid", SqlDbType.Bit);
        para[4].Value = Bill.IsPaid;       
        if (db.execute("InsertBill", para) > 0)
        {
            result = true;
        }
        else
        {
            result = false;
        }
        return result;
    }

    public bool updateBillPaidById(Bill bill)
    {
        bool result;
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@BillId", SqlDbType.Int);
        para[0].Value = bill.BillId;
        if (db.execute("UpdateBillPaidById", para) > 0)
        {
            result = true;
        }
        else
        {
            result = false;
        }
        return result;
    }
    
    public bool deleteBillById(int BillId)
    {
        bool result;
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@BillId", SqlDbType.Int);
        para[0].Value = BillId;
        if (db.execute("DeleteBillById", para) > 0)
        {
            result = true;
        }
        else
        {
            result = false;
        }
        return result;
    }

    public bool deleteBillByPolicyId(int PolicyId)
    {
        bool result;
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@PolicyId", SqlDbType.Int);
        para[0].Value = PolicyId;
        if (db.execute("DeleteBillByPolicyId", para) > 0)
        {
            result = true;
        }
        else
        {
            result = false;
        }
        return result;
    }
    public float getBillPaidByPolicyId(int PolicyId)
    {
        float sum=0;
        Bill Bill = new Bill();
        DataTable dt = new DataTable();
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@PolicyId", SqlDbType.Int);
        para[0].Value = PolicyId;
        dt = db.executeSelect("GetBillByPolicyId", para);
        //Bill.BillId = int.Parse(dt.Rows[0][0].ToString());
        //Bill.PolicyId = int.Parse(dt.Rows[0][1].ToString());
        //Bill.Cost = float.Parse(dt.Rows[0][2].ToString());
        //Bill.Note = dt.Rows[0][3].ToString();
        //Bill.DateOfCreate = DateTime.Parse(dt.Rows[0][4].ToString());
        //Bill.IsPaid = bool.Parse(dt.Rows[0][5].ToString());
        for (int i=0;i<dt.Rows.Count;i++)
        {
            if (bool.Parse(dt.Rows[0][5].ToString())==true)
            {
                sum+=float.Parse(dt.Rows[0][2].ToString());
            }
        }
        return sum;
    }
}
